package com.imooc.utils;


import com.imooc.excel.ExcelVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;


/**
 * 导出
 * @author 顾志杰
 * @date 2021/1/24-20:01
 */
@Slf4j
public class ExportUtil {

    public static void getValue(List<ExcelVO> list, HttpServletResponse response) throws IOException {
        //1.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 填写须知
        //CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 0, 12);//起始行,结束行,起始列,结束列

        //填写须知
        // XSSFCellStyle headStyle = createCellStyle(workbook, (short) 18, true, false);
        //标题样式
        XSSFCellStyle colStyle = createCellStyle(workbook, (short) 11, false, true);
        //内容样式
        XSSFCellStyle cellStyle = createCellStyle(workbook, (short) 10, false, true);

        //2.创建工作表
        XSSFSheet sheet = workbook.createSheet("Sheet1");
        //2.1加载合并单元格对象
        //sheet.addMergedRegion(callRangeAddress);

        //设置默认列宽
        sheet.setDefaultColumnWidth(18);

        mergedRegion(sheet);

        //3.2创建列标题;并且设置列标题
        XSSFRow row2 = sheet.createRow(0);
        String[] titles = {"序号", "电话号码", "姓名", "身份证号",
                "是否可联系上（是/否)", "风险地区来源地市", "是否在本地（是/否）",
                "在本地所在区县", "在本地详细地址", "离开本地现在所在省市",
                "是否进行核酸检测（是/否）", "核酸检测结果（阴性/阳性/已检测未出结果","管控措施（是/否)","管控措施（是/否)","管控措施（是/否)"
                ,"是否管控（是/否）","备注(现状)"};//""为占位字符串
        String[] titless = {"集中隔离（是/否）","居家隔离（是/否）","健康监测（是/否）"};
        XSSFCell titleRow1 = row2.createCell(0);
        //加载单元格样式
        titleRow1.setCellStyle(colStyle);
        titleRow1.setCellValue("省防指下发风险人员排查名单");
        XSSFRow titleRow2 = sheet.createRow(1);
        for (int i = 0; i < titles.length; i++) {
            XSSFCell cell2 = titleRow2.createCell(i);
            //加载单元格样式
            cell2.setCellStyle(colStyle);
            cell2.setCellValue(titles[i]);
        }
        XSSFRow titleRow3 = sheet.createRow(2);
        for (int i = 0; i < titless.length; i++) {
            XSSFCell cell2 = titleRow3.createCell(i+12);
            //加载单元格样式
            cell2.setCellStyle(colStyle);
            cell2.setCellValue(titless[i]);
        }


        //4.操作单元格;将用户列表写入excel
        if (list != null) {
            for (int j = 0; j < list.size(); j++) {
                //创建数据行,前面有两行,头标题行和列标题行
                XSSFRow row3 = sheet.createRow(j + 3);
                //序号
                XSSFCell cell0 = row3.createCell(0);
                cell0.setCellStyle(cellStyle);
                cell0.setCellValue(j+1);
                // 电话号码
                XSSFCell cell1 = row3.createCell(1);
                cell1.setCellStyle(cellStyle);
                cell1.setCellValue(StringUtils.isNotBlank(list.get(j).getMobilePhone()));

                // 姓名
                XSSFCell cell2 = row3.createCell(2);
                cell2.setCellStyle(cellStyle);
                cell2.setCellValue(StringUtils.isNotBlank(list.get(j).getPersonName()));

                // 身份证号
                XSSFCell cell3 = row3.createCell(3);
                cell3.setCellStyle(cellStyle);
                cell3.setCellValue(StringUtils.isNotBlank(list.get(j).getIdNo()));


                // 是否可联系上（是/否）
                XSSFCell cell4 = row3.createCell(4);
                cell4.setCellStyle(cellStyle);
                cell4.setCellValue("1".equals(list.get(j).getIsLink())?"是":"否");

                // 风险地区来源地市
                XSSFCell cell5 = row3.createCell(5);
                cell5.setCellStyle(cellStyle);
                cell5.setCellValue(list.get(j).getRiskSource());

                // 是否在本地（是/否）
                XSSFCell cell6 = row3.createCell(6);
                cell6.setCellStyle(cellStyle);
                cell6.setCellValue("1".equals(list.get(j).getIsLocal())?"是":"否");

                // 在本地所在区县
                XSSFCell cell7 = row3.createCell(7);
                cell7.setCellStyle(cellStyle);
                cell7.setCellValue(list.get(j).getInRegion());

                // 在本地详细地址
                XSSFCell cell8 = row3.createCell(8);
                cell8.setCellStyle(cellStyle);
                cell8.setCellValue(list.get(j).getAddressInfo());

                // 离开本地现在所在省市
                XSSFCell cell9 = row3.createCell(9);
                cell9.setCellStyle(cellStyle);
                cell9.setCellValue(list.get(j).getInProvince());

                // 是否进行核酸检测（是/否)
                XSSFCell cell10 = row3.createCell(10);
                cell10.setCellStyle(cellStyle);
                cell10.setCellValue("1".equals(list.get(j).getIsDetection())?"是":"否");

                // 核酸检测结果（0阴性，1阳性，2已检测未出结果）
                XSSFCell cell11 = row3.createCell(11);
                cell11.setCellStyle(cellStyle);
                cell11.setCellValue("1".equals(list.get(j).getDetectResult())?"阳性":"0".equals(list.get(j).getDetectResult())?"阴性":"已检测未出结果");

                // 集中隔离
                XSSFCell cell12 = row3.createCell(12);
                cell12.setCellStyle(cellStyle);
                cell12.setCellValue("1".equals(list.get(j).getIsCentralIso())?"是":"否");

                // 居家隔离
                XSSFCell cell13 = row3.createCell(13);
                cell13.setCellStyle(cellStyle);
                cell13.setCellValue("1".equals(list.get(j).getIsHomeIso())?"是":"否");

                // 健康监测
                XSSFCell cell14 = row3.createCell(14);
                cell14.setCellStyle(cellStyle);
                cell14.setCellValue("1".equals(list.get(j).getIsHealthMonitor())?"是":"否");

                // 是否管控（是/否）
                XSSFCell cell15 = row3.createCell(15);
                cell15.setCellStyle(cellStyle);
                cell15.setCellValue("1".equals(list.get(j).getIsHealthMonitor())?"是":"否");

                // 备注
                XSSFCell cell16 = row3.createCell(16);
                cell16.setCellStyle(cellStyle);
                cell16.setCellValue(list.get(j).getRemark());

            }
        }

        getMultipartFile(workbook, "排查人员.xlsx",response);
    }



    /**
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     */
    private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short fontsize, boolean flag, boolean flag1) {
        // TODO Auto-generated method stub
        XSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if (flag1) {
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        }

        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //创建字体
        XSSFFont font = workbook.createFont();
        //是否加粗字体
        if (flag) {
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }

    private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short fontsize, boolean bold, boolean center, boolean right) {
        // TODO Auto-generated method stub
        XSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if (center) {
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        }

        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //创建字体
        XSSFFont font = workbook.createFont();
        //是否加粗字体
        if (bold) {
            font.setBold(true);
        }
        if (right) {
            style.setAlignment(HorizontalAlignment.RIGHT);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }



    private static void getMultipartFile(XSSFWorkbook workbook, String name, HttpServletResponse response) throws IOException {
        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        String encode = URLEncoder.encode(name, "utf-8");
//        name = new String(name.getBytes("UTF-8"), "ISO-8859-1");
        response.setHeader("Content-disposition",  "attachment;filename="+encode+";"+"filename*=utf-8''"+encode);
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/msexcel");
        workbook.write(output);
        workbook.close();
        output.close();
    }


    /**
     * 合并单元格
     * 行  列   第几到第几列合并
     * @param sheet
     */
    private static void mergedRegion(XSSFSheet sheet) {
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 16));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 11, 11));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 14));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 15, 15));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 16, 16));
    }

}
